package com.lovcreate.Controller;

import com.lovcreate.utils.ExcelParser;
import com.lovcreate.utils.XlsParser;
import com.lovcreate.utils.XlsxParser;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.FutureTask;

/**
 * Created by Administrator on 2016/7/28.
 */
@WebServlet(name = "ExcelToStudentTable",urlPatterns = {"/ExcelToStudentTable"})
public class ExcelToStudentTable extends HttpServlet {

    private boolean isMultipart;
    private String filePath;
    private int maxFileSize = 50 * 1024;
    private int maxMemSize = 4 * 1024;
    private File file;

    @Override
    public void init() throws ServletException {
        // 获取文件的存储位置
        filePath = getServletContext().getInitParameter("file-upload");
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // 检查我们有一个文件上传请求
        isMultipart = ServletFileUpload.isMultipartContent(request);
        response.setContentType("text/html; charset=utf-8");
        PrintWriter out = response.getWriter();
        if( !isMultipart ){
            out.println("<html>");
            out.println("<head>");
            out.println("<title>Student upload</title>");
            out.println("</head>");
            out.println("<body>");
            out.println("<p>No file uploaded</p>");
            out.println("</body>");
            out.println("</html>");
            return;
        }
        DiskFileItemFactory factory = new DiskFileItemFactory();
        // 文件大小的最大值将被存储在内存中
        factory.setSizeThreshold(maxMemSize);
        // Location to save data that is larger than maxMemSize.
        factory.setRepository(new File("F:\\temp"));

        // 创建一个新的文件上传处理程序
        ServletFileUpload upload = new ServletFileUpload(factory);
        // 允许上传的文件大小的最大值
        upload.setSizeMax(maxFileSize);

        // PreparedStatement ps = null;

        try {
            // 解析请求，获取文件项
            List fileItems = upload.parseRequest(request);
            // 处理上传的文件项
            Iterator it = fileItems.iterator();

            out.println("<html>");
            out.println("<head>");
            out.println("<title>Student upload</title>");
            out.println("</head>");
            out.println("<body>");
            while ( it.hasNext () )
            {
                FileItem fi = (FileItem)it.next();
                if ( !fi.isFormField () )
                {
                    // 获取上传文件的参数
                    String fileName = fi.getName();

                    // 写入文件
                    if( fileName.lastIndexOf("\\") >= 0 ){
                        file = new File( filePath +
                                fileName.substring( fileName.lastIndexOf("\\"))) ;
                    }else{
                        file = new File( filePath +
                                fileName.substring(fileName.lastIndexOf("\\")+1)) ;
                    }
                    fi.write( file ) ;
                    // 将 上传文件的绝对路径 赋值给 filePath
                    filePath = filePath + fileName;
                    out.println("Uploaded Filename: " + fileName + "<br>");

                    // 取得文件名的后缀
                    String suffix = filePath.substring(filePath.lastIndexOf(".") + 1, filePath.length());
                    System.out.println(suffix);

                    ExcelParser excelParser;
                    excelParser = new XlsParser();      // 默认是 .xls 文件，文件类型不对要加响应处理 ！
                    if("xls".equalsIgnoreCase(suffix)) {
                        excelParser = new XlsParser();
                        out.println("<br />.xls文件");
                    }else if("xlsx".equalsIgnoreCase(suffix)) {
                        excelParser = new XlsxParser();
                        out.println("<br />.xlsx文件");
                    } else {
                        // 加重新上传文件的操作
                        out.println("文件类型不正确，您需要传输后缀名为xls或者xlsx的Excel文件 !");
                    }

                    List<Map<String,String>> list = excelParser.excelParser(filePath);
                    System.out.println(list);
                    Connection conn = (Connection) getServletContext().getAttribute("conn");    // 通过上下文属性获得数据库连接
                    String sql = "insert into student_info(no,name,age,score) values(?,?,?,?)";

                    // 线程池初始化线程
                    ExecutorService executorService = Executors.newFixedThreadPool(10);

                    // 类似与 run 方法的实现，Callable 是一个接口，在call中手写逻辑代码
                    Callable<Boolean> callable = new Callable<Boolean>() {
                        @Override
                        public Boolean call() throws Exception {
                            Boolean res = false;
                            PreparedStatement ps = null;
                            for(int i = 0; i < list.size(); i++) {
                                ps = conn.prepareStatement(sql);
                                Map map = list.get(i);
                                for(Object key : map.keySet()) {
                                    String value = map.get(key).toString();
                                    if(key.toString().equals("no")) {
                                        ps.setString(1,value);
                                    } else if(key.toString().equals("name")) {
                                        ps.setString(2,value);
                                    } else if(key.toString().equals("age")) {
                                        ps.setString(3,value);
                                    } else if(key.toString().equals("score")) {
                                        ps.setString(4,value);
                                    }
                                }
                                boolean flag = ps.execute();        // flag值为 false 为语句插入成功
                                res = res || flag;
                                if(!flag) {
                                    // 语句执行成功进行的操作
                                    System.out.println("数据插入成功！");
                                }
                            }
                            ps.close();     // 关闭资源
                            return res;
                        }
                    };

                    // 创建一个异步任务
                    FutureTask<Boolean> futureTask = new FutureTask<Boolean>(callable);
                    // 提交异步任务到线程池，让线程池管理任务
                    executorService.submit(futureTask);

                    if(!futureTask.get()) {
                        out.println("数据全部插入成功！");
                    }

                    // 清理线程池
                    executorService.shutdown();

                }
            }
            out.println("</body>");
            out.println("</html>");

        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

}
